package timetable.io;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Vector;

import timetable.exceptions.DriverNotFoundException;
import timetable.interfaces.ProgressObservable;

public class SQLDatabase extends ProgressObservable
{
	public final static String[] SUPPORTED_DB_SYSTEMS = {
		"MySQL",
	};
	public final static String[] DB_DRIVERS = {
		"com.mysql.jdbc.Driver",
	};
	
	// CONSTANTS
	private static final int DEFAULT_CURSUSNAAM_SIZE = 200;
	private static final int DEFAULT_AANVANG_SIZE = 5;
	private static final int DEFAULT_KLAS_SIZE = 50;
	private static final int DEFAULT_LOKAAL_SIZE = 50;
	private static final int DEFAULT_DUUR_SIZE = 5;
	private static final int DEFAULT_DOCENT_SIZE = 280;
	private static final int DEFAULT_DAG_SIZE = 9;
	private static final int DEFAULT_MAX_ERROR = 200;
	
	
	private String url;
	private String username;
	private String password;
	private String driver;
	private String timetableName;
	private Connection con;
	
	public SQLDatabase(String driver, String url, String uname, String password, String dbTable)
	{
		super();
		url = url.trim();
		///if (url.lastIndexOf("/") != url.length()-1)
		//{
		//	url = url + "/";
		//}
		//url = url + dbName;
		
		this.setUrl(url);
		this.setUname(uname);
		this.setPassword(password);
		this.setDriver(driver);
		this.timetableName = dbTable;
	}
	
	private void openConnection() throws SQLException, DriverNotFoundException
	{
		try
		{
			Class.forName(this.driver);
			con = DriverManager.getConnection(this.url,this.username, this.password);
		}
		catch (ClassNotFoundException e)
		{
			throw new DriverNotFoundException("Driver not found", "The driver for the SQL-database could not be found.");
		}
	}
	
	public void testConnection() throws SQLException, DriverNotFoundException
	{
		this.openConnection();
		this.closeConnection();
	}
	
	private void closeConnection() throws SQLException
	{
		this.con.close();
	}
	
	public void setUrl(String url) {
		this.url = url;
	}

	public String getUrl() {
		return url;
	}

	public void setUname(String uname) {
		this.username = uname;
	}

	public String getUname() {
		return username;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getPassword() {
		return password;
	}

	public void setDriver(String driver) {
		this.driver = driver;
	}

	public String getDriver() {
		return driver;
	}
	
	public void createTimeTable(TimeTableDataFile data) throws SQLException, DriverNotFoundException
	{
		int max_aanvang = 0;
		int max_duur = 0;
		int max_docent = 0;
		int max_cursusnaam = 0;
		int max_klas = 0;
		int max_lokaal = 0;
		int max_dag = 0;
		int max_error = 0;
		
		data.reset();
		while (data.hasNext())
		{
			TimeTableEntry e = data.next();
			if (!e.isInvalid())
			{
				if (e.getAanvang().length() > max_aanvang) 			max_aanvang = e.getAanvang().length();
				if (e.getDuur().length() > max_duur) 				max_duur = e.getDuur().length();
				if (e.getDocent().length() > max_docent) 			max_docent = e.getDocent().length();
				if (e.getKlas().length() > max_klas)				max_klas = e.getKlas().length();
				if (e.getCursusnaam().length() > max_cursusnaam) 	max_cursusnaam = e.getCursusnaam().length();
				if (e.getLokaal().length() > max_lokaal) 			max_lokaal = e.getLokaal().length();
				if (e.getDag().length() > max_dag) 					max_dag = e.getDag().length();
			} else
			{
				if (e.getError().getMessage().length() > max_error) max_error = e.getError().getMessage().length();
			}
				
		}
		
		//System.out.println("Length of Fields:\n"
		//		+ "Maximum Aanvang: " + max_aanvang + "\n"
		//		+ "Maximum Duur: " + max_duur + "\n"
		//		+ "Maximum Docent: " + max_docent + "\n"
		//		+ "Maximum Cursusnaam: " + max_cursusnaam + "\n"
		//		+ "Maximum Klas: " + max_klas + "\n"
		//		+ "Maximum Lokaal: " + max_lokaal + "\n"
		//		+ "Maximum Dag: " + max_dag
		//);
		
		this.createTimeTable(max_duur, max_aanvang, max_docent, max_cursusnaam, max_klas, max_lokaal, max_dag, max_error);
	}
	
	public void createTimeTable() throws SQLException, DriverNotFoundException
	{
		createTimeTable(DEFAULT_DUUR_SIZE, DEFAULT_AANVANG_SIZE, DEFAULT_DOCENT_SIZE, DEFAULT_CURSUSNAAM_SIZE, DEFAULT_KLAS_SIZE, DEFAULT_LOKAAL_SIZE, DEFAULT_DAG_SIZE, DEFAULT_MAX_ERROR);
	}
	
	private void createTimeTable(int max_duur, int max_aanvang, int max_docent, int max_cursusnaam, int max_klas, int max_lokaal, int max_dag, int max_error) throws SQLException, DriverNotFoundException
	{
		openConnection();
		String query = "CREATE TABLE " + this.timetableName + " (" +
			DataField.ID 			+ " int(10) NOT NULL AUTO_INCREMENT," +
			DataField.START 		+ " varchar("	+ max_aanvang 		+ ") DEFAULT NULL," +
			DataField.DURATION 		+ " varchar("	+ max_duur 			+ ") DEFAULT NULL," +
			DataField.INSTRUCTOR 	+ " varchar("	+ max_docent		+ ") DEFAULT NULL," +
			DataField.COURSE		+ " varchar("	+ max_cursusnaam 	+ ") DEFAULT NULL," +
			DataField.CLASS 		+ " varchar("	+ max_klas			+ ") DEFAULT NULL," +
			DataField.CLASSROOM		+ " varchar("	+ max_lokaal		+ ") DEFAULT NULL," +
			DataField.DAY			+ " varchar("	+ max_dag			+ ") DEFAULT NULL," +
			DataField.ERROR_MESSAGE + " varchar("	+ max_error			+ ") DEFAULT NULL," +
				"PRIMARY KEY (" + DataField.ID + ")" +
			") ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;";

		Statement s = con.createStatement();
		s.executeUpdate(query);
		closeConnection();
	}
	
	public void dropTimeTable() throws SQLException, DriverNotFoundException
	{
		this.openConnection();
		String query = "DROP TABLE IF EXISTS " + this.timetableName + ";";
		con.createStatement().executeUpdate(query);
		this.closeConnection();
	}
	
	public void emptyTable() throws SQLException, DriverNotFoundException
	{
		this.openConnection();
		Statement s = con.createStatement();
		s.executeUpdate("DELETE FROM " + this.timetableName + ";");
		s.executeUpdate("ALTER TABLE " + this.timetableName + " AUTO_INCREMENT=1;");
		this.closeConnection();
	}
	
	public void fillTimeTable(TimeTableDataFile data) throws SQLException, DriverNotFoundException
	{
		int size = data.getEntryCount();
		int done = 0;
		
		openConnection();
		String query = "";
		data.reset();
		while (data.hasNext())
		{
			TimeTableEntry e = data.next();
			Statement stmt = con.createStatement();
			if (!e.isInvalid())
			{
			query = "INSERT INTO " + this.timetableName + " (" 	+ DataField.START + "," 
																+ DataField.DURATION + "," 
																+ DataField.INSTRUCTOR + "," 
																+ DataField.COURSE + "," 
																+ DataField.CLASS + "," 
																+ DataField.CLASSROOM + "," 
																+ DataField.DAY
																+ ") VALUES (\""
					+ e.getAanvang() + "\",\""
					+ e.getDuur() + "\",\""
					+ e.getDocent() + "\",\""
					+ e.getCursusnaam() + "\",\""
					+ e.getKlas() + "\",\""
					+ e.getLokaal() + "\",\""
					+ e.getDag()
					+ "\");";
			}
			else
			{
				query = "INSERT INTO " + this.timetableName + " (" + DataField.ERROR_MESSAGE + ") VALUES (\"" + e.getError().getMessage() + "\");";
			}
			stmt.executeUpdate(query);
			done++;
			//System.out.println(new Integer( (int) ( (((double) done) / ((double) size)) * 100 )))
			notifyObservers(new Integer( (int) ( (double) done / (double) size * 100 )));
		}
		closeConnection();
	}
	
	private List<TimeTableEntry> getRecordsFromResultSet(ResultSet r) throws SQLException
	{
		Vector<TimeTableEntry> records = new Vector<TimeTableEntry>();
		int id = 1;
		while (r.next())
		{
			TimeTableEntry e = new TimeTableEntry(Integer.parseInt(r.getString(DataField.ID)));
			e.setAanvang(r.getString(DataField.START));
			e.setDuur(r.getString(DataField.DURATION));
			e.setDocent(r.getString(DataField.INSTRUCTOR));
			e.setCursusnaam(r.getString(DataField.COURSE));
			e.setKlas(r.getString(DataField.CLASS));
			e.setDag(r.getString(DataField.DAY));
			e.setLokaal(r.getString(DataField.CLASSROOM));
			if (r.getString(DataField.ERROR_MESSAGE) != null)
				e.setInvalid(r.getString(DataField.ERROR_MESSAGE));
			records.add(e);
			id++;
		}
		return records;
	}
	
	private List<TimeTableEntry> getRecordsByColumn(String column, String item, String dag)
	{
		List<TimeTableEntry> records = null;
		String query = "SELECT "	+ DataField.START + "," 
									+ DataField.DURATION + ","
									+ DataField.INSTRUCTOR + ","
									+ DataField.COURSE + "," 
									+ DataField.CLASS + "," 
									+ DataField.CLASSROOM + ","
									+ DataField.DAY
									+ " FROM " + this.timetableName + " WHERE " + column + " LIKE \"%" + item + "%\" AND "+ DataField.DAY + "=\"" + dag + "\";";
		
		try {
			this.openConnection();
			Statement s = con.createStatement();
			
			ResultSet r = s.executeQuery(query);
			records = getRecordsFromResultSet(r);
			
			this.closeConnection();
		} catch (DriverNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return records;
	}
	
	public List<TimeTableEntry> getRecordsByDocent(String docent, String dag)
	{
		return getRecordsByColumn(DataField.INSTRUCTOR, docent, dag);
	}
	
	public List<TimeTableEntry> getRecordsByKlas(String klas, String dag)
	{
		return getRecordsByColumn(DataField.CLASS, klas, dag);
	}
	
	public List<TimeTableEntry> getRecordsByVak(String vak, String dag)
	{
		return getRecordsByColumn(DataField.COURSE, vak, dag);
	}
	
	public List<TimeTableEntry> getErrors()
	{
		List<TimeTableEntry> records = null;
		String query = "SELECT * FROM " + this.timetableName + " WHERE " + DataField.ERROR_MESSAGE + " IS NOT NULL;"; 
		
		try {
			this.openConnection();
			Statement s = con.createStatement();
			
			ResultSet r = s.executeQuery(query);
			records = getRecordsFromResultSet(r);
			
			this.closeConnection();
		} catch (DriverNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return records;
	}
}
